package top.zk123.chain.mapper;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import top.zk123.chain.bean.Certificate;

import java.util.List;

public interface CertificateMapper extends JpaRepository<Certificate, String> {
    /**
     * 依照InstitutionId 进行查询, 并且进行分页操作
     *
     * @param institutionId 机构标识
     * @param pageable      是否进行分页
     * @return Page 对象列表的子列表
     */
    @Query(value = "select * from certificate where institution_id = :institutionId", nativeQuery = true)
    Page<Certificate> findByInstitutionId(String institutionId, Pageable pageable);

    /**
     * 分页查询 导入的证书， 证书状态都为 0
     *
     * @param institutionId
     * @param certType
     * @param name
     * @param sex
     * @param userId
     * @param certId
     * @param issueDate
     * @param institution
     * @param pageable
     * @return
     */
    @Query(value = "select * from certificate  where institution_id = :institutionId and cert_type like %:certType% and cert_state = 0 and name like %:name% and sex like %:sex% and user_id like %:userId% and cert_id like %:certId% and issue_date like %:issueDate% and institution like %:institution%", nativeQuery = true)
    Page<Certificate> findByInstitutionId(String institutionId, String certType, String name, String sex, String userId,
                                          String certId, String issueDate, String institution, Pageable pageable);

    @Query(value = "select * from certificate where cert_id = :certId", nativeQuery = true)
    Certificate findByCertId(String certId);

    @Query(value = "select * from certificate where uid = :certUId", nativeQuery = true)
    Certificate findByCertUid(long certUId);

    // 查询库中所有未上链证书
    @Query(value = "select uid from certificate where cert_state = 0", nativeQuery = true)
    List<Long> findCertsNotOnTheChain();


    @Query(value = "select * from certificate where institution_id = :institutionId and cert_id = :certId", nativeQuery = true)
    Certificate findByInstitutionIdAndCertId(String institutionId, String certId);

    @Query(value = "select * from certificate where cert_state = :certState", nativeQuery = true)
    List<Certificate> findByCertState(int certState);

    @Query(value = "select * from certificate where uid = :uid", nativeQuery = true)
    Certificate findByUid(long uid);

    @Query(value = "select * from certificate where uid = :uid and cert_state!=3", nativeQuery = true)
    Certificate findValidByUid(long uid);

    // 模糊分页查询已颁发证书
    @Query(value = "select * from certificate where institution_id = :institutionId and cert_state != 0 and cert_type like %:certType% and name like %:name% and sex like %:sex% and user_id like %:userId% and cert_id like %:certId% and issue_date like %:issueDate% and cert_state = :certState", nativeQuery = true)
    Page<Certificate> findByInstitutionId(String institutionId, String certType, String name, String sex, String userId, String certId, String issueDate, int certState, Pageable pageable);

    // 查询全部的已颁发证书
    @Query(value = "select * from certificate where institution_id = :institutionId and cert_state != 0 and cert_type like %:certType% and name like %:name% and sex like %:sex% and user_id like %:userId% and cert_id like %:certId% and issue_date like %:issueDate%", nativeQuery = true)
    Page<Certificate> findAllByInstitutionId(String institutionId, String certType, String name, String sex, String userId, String certId, String issueDate, Pageable pageable);


}
